DROP SEQUENCE BOOK_ID;
DROP TABLE BOOKS;

CREATE SEQUENCE BOOK_ID;
CREATE TABLE BOOKS (
    BOOK_ID INTEGER NOT NULL,
    TITLE VARCHAR2(40 CHAR) NOT NULL,
    AUTHOR VARCHAR2(40 CHAR),
    PUB_DATE DATE NOT NULL,
    PRICE NUMBER(12, 2) NOT NULL,
    AMOUNT INTEGER NOT NULL,
    PRIMARY KEY (BOOK_ID),
    UNIQUE (TITLE)
);

CREATE OR REPLACE PROCEDURE SAVE_BOOK(
    V_RET      OUT SYS_REFCURSOR,
    V_BOOK_ID  IN  INTEGER,
    V_TITLE    IN  VARCHAR2,
    V_AUTHOR   IN  VARCHAR2,
    V_PUB_DATE IN  DATE,
    V_PRICE    IN  NUMERIC,
    V_AMOUNT   IN  INTEGER
) AS
    V_BOOK_ID2 INTEGER;
BEGIN
    IF V_BOOK_ID = 0 THEN
        V_BOOK_ID2 := BOOK_ID.NEXTVAL;
        INSERT INTO BOOKS (BOOK_ID, TITLE, AUTHOR, PUB_DATE, PRICE, AMOUNT)
        VALUES(V_BOOK_ID2, V_TITLE, V_AUTHOR, V_PUB_DATE, V_PRICE, V_AMOUNT);
    ELSE
        V_BOOK_ID2 := V_BOOK_ID;
        UPDATE BOOKS SET TITLE = TITLE, AUTHOR = AUTHOR, PUB_DATE = PUB_DATE, PRICE = PRICE, AMOUNT = AMOUNT
        WHERE BOOK_ID = V_BOOK_ID2;
    END IF;

    OPEN V_RET FOR
    SELECT BOOK_ID, TITLE, AUTHOR, PUB_DATE, PRICE, AMOUNT
    FROM BOOKS
    WHERE BOOK_ID = V_BOOK_ID2;
END SAVE_BOOK;

DROP TABLE MY_USER_PROP;
DROP TABLE MY_USER_ROLE;
DROP TABLE MY_USER;
DROP TABLE MY_ROLE_PERM;
DROP TABLE MY_ROLE;

CREATE TABLE MY_ROLE
(
    R_NAME VARCHAR2(200 CHAR) NOT NULL,
	R_TITLE VARCHAR2(200 CHAR) NOT NULL,
	R_DESCRIPTION VARCHAR2(1000 CHAR),
	R_ENABLED NUMBER DEFAULT 1 NOT NULL
);

CREATE TABLE MY_ROLE_PERM
(
    R_NAME VARCHAR2(200 CHAR) NOT NULL,
    PERM_ID NUMBER NOT NULL
);

CREATE TABLE MY_USER
(
    U_NAME VARCHAR2(800 CHAR) NOT NULL,
	U_PWD VARCHAR2(800 CHAR),
	U_CREATE_DATE DATE NOT NULL,
	U_LAST_MODIFIED_DATE DATE NOT NULL,
	U_ENABLED NUMBER DEFAULT 1 NOT NULL
);

CREATE TABLE MY_USER_PROP
(
    U_NAME VARCHAR2(200 CHAR) NOT NULL,
	P_KEY VARCHAR2(200 CHAR) NOT NULL,
	P_VALUE VARCHAR2(1000 CHAR),
	P_TYPE NUMBER DEFAULT 0 NOT NULL
);

CREATE TABLE MY_USER_ROLE
(
    U_NAME VARCHAR2(200 CHAR) NOT NULL,
	R_NAME VARCHAR2(200 CHAR) NOT NULL
);

CREATE UNIQUE INDEX MY_ROLE_PK ON MY_ROLE (R_NAME);
CREATE UNIQUE INDEX MY_ROLE_PERM_PK ON MY_ROLE_PERM (R_NAME, PERM_ID);
CREATE UNIQUE INDEX MY_USER_PK ON MY_USER (U_NAME);
CREATE UNIQUE INDEX MY_USER_PROP_PK ON MY_USER_PROP (U_NAME, P_KEY);
CREATE UNIQUE INDEX MY_USER_ROLE_PK ON MY_USER_ROLE (U_NAME, R_NAME);

ALTER TABLE MY_ROLE ADD CONSTRAINT MY_ROLE_PK PRIMARY KEY (R_NAME);
ALTER TABLE MY_ROLE_PERM ADD CONSTRAINT MY_ROLE_PERM_PK PRIMARY KEY (R_NAME, PERM_ID);
ALTER TABLE MY_USER ADD CONSTRAINT MY_USER_PK PRIMARY KEY (U_NAME);
ALTER TABLE MY_USER_PROP ADD CONSTRAINT MY_USER_PROP_PK PRIMARY KEY (U_NAME, P_KEY);
ALTER TABLE MY_USER_ROLE ADD CONSTRAINT MY_USER_ROLE_PK PRIMARY KEY (U_NAME, R_NAME);

ALTER TABLE MY_ROLE_PERM ADD CONSTRAINT MY_ROLE_PERM_FK1 FOREIGN KEY (R_NAME) REFERENCES MY_ROLE (R_NAME) ON DELETE CASCADE ENABLE;
ALTER TABLE MY_USER_PROP ADD CONSTRAINT MY_USER_PROP_FK1 FOREIGN KEY (U_NAME) REFERENCES MY_USER (U_NAME) ON DELETE CASCADE ENABLE;
ALTER TABLE MY_USER_ROLE ADD CONSTRAINT MY_USER_ROLE_FK1 FOREIGN KEY (U_NAME) REFERENCES MY_USER (U_NAME) ON DELETE CASCADE ENABLE;
ALTER TABLE MY_USER_ROLE ADD CONSTRAINT MY_USER_ROLE_FK2 FOREIGN KEY (R_NAME) REFERENCES MY_ROLE (R_NAME) ON DELETE CASCADE ENABLE;

